<?php
namespace app\crm\model;
use think\Model;
use think\Db;
use think\Cache;
use think\Loader;

class SaleReport extends Model
{   
    /**
     * 发货列表
     * @return [type] [description]
     */
    public function goodsSaleReportData()
	{
		$page = $_REQUEST["page"];
		$limit = $_REQUEST["limit"];
		$createtime_start = $_REQUEST['createtime_start'];
		$createtime_end = $_REQUEST['createtime_end'];
		$create_user = $_REQUEST['create_user'];
		$dept_id = $_REQUEST['dept_id'];
		$username = $_SESSION["user_info"]["username"];
		$d_id = $_SESSION['user_info']['d_id'];
		$arrDep = $this->getDepTreeData();
		$deptst = $this->getMeAndSubDeptID($arrDep,$dept_id);  //取上级部门
		$deptSet = rtrim($deptst,",");
		
		$deptst2 = $this->getMeAndSubDeptID($arrDep,$d_id);  //取上级部门
		$deptSet2 = rtrim($deptst2,",");
		
		if(!$dept_id){
			$deptId = $deptSet2;
		}else{
			$deptId = $deptSet;
		}
		$where = "1 ";
		$where .= empty($createtime_start) ? "" : " AND createtime >= '$createtime_start'";
		$where .= empty($createtime_end) ? "" : " AND createtime <= '$createtime_end'";
		if($username != "admin" || $dept_id){
			$where .= " AND dept_id in ($deptId)";
		}
		
		$fields = "og.goods_id,og.order_id,og.goods_num,g.good_name,g.good_num,
			sum(og.goods_num) as total_good_sales_num,
			sum(og.goods_money) as total_good_sales_money,
			sum(if(o.order_status = '1',og.goods_num,0)) as actual_good_sales_num,
			sum(if(o.order_status = '1',og.goods_money,0)) as actual_good_sales_money,
			sum(if(o.order_status != '1',og.goods_num,0)) as other_good_sales_num,
			sum(if(o.order_status != '1',og.goods_money,0)) as other_good_sales_money
		";
		
		//print_r($fields);
		$arrCount = Db::table('xy_order_goods')->alias('og')->join('xy_order_info o','og.order_id = o.id')->join('xy_shop_goods g','og.goods_id = g.id')->Distinct(true)->field("og.goods_id")->where($where)->select();
		$count = count($arrCount);
		$sql = "SELECT ".$fields." FROM xy_order_info o, xy_order_goods og, xy_shop_goods g WHERE og.order_id = o.id AND og.goods_id = g.id AND ".$where."  GROUP BY og.`goods_id` ORDER BY og.goods_id asc LIMIT ".($page-1)*$limit.",".$limit;
		//$dataList = Db::name('order_info')->fetchSql(true)->field($fields)->where($where)->limit(($page-1)*$limit,$limit)->order("createname asc")->group("createname")->select();
		$dataList = Db::query($sql);
		
		// foreach($dataList as $key=>&$val){
		// 	$val["dept_name"] = $department[$val["dept_id"]]["department_name"];
		// 	$val["cn_name"] = $users[$val["createname"]]["cn_name"];
		// 	//实际销售单数/总订单数
		// 	$val["audit_rate2"] = ($val["actual_sales_num"]/$val["total_order_num"])*100;
		// 	$val["audit_rate"] = round($val["audit_rate2"],2)."%";;  //审核率
		// }
		
		$rowsList = count($dataList) ? $dataList : false;
        $arrCate["code"] = 0;
        $arrCate["msg"] = '';
        $arrCate["count"] = $count;
        $arrCate["data"] = $rowsList;
        return $arrCate;
	}


    public function programSaleReportData()
	{
		$page = $_REQUEST["page"];
		$limit = $_REQUEST["limit"];
		$createtime_start = $_REQUEST['createtime_start'];
		$createtime_end = $_REQUEST['createtime_end'];
		$dept_id = $_REQUEST['dept_id'];
		$username = $_SESSION["user_info"]["username"];
		$d_id = $_SESSION['user_info']['d_id'];
		$arrDep = $this->getDepTreeData();
		$deptst = $this->getMeAndSubDeptID($arrDep,$dept_id);  //取上级部门
		$deptSet = rtrim($deptst,",");
		
		$deptst2 = $this->getMeAndSubDeptID($arrDep,$d_id);  //取上级部门
		$deptSet2 = rtrim($deptst2,",");
		
		if(!$dept_id){
			$deptId = $deptSet2;
		}else{
			$deptId = $deptSet;
		}
		$where = "1 ";
		$where .= empty($createtime_start) ? "" : " AND createtime >= '$createtime_start'";
		$where .= empty($createtime_end) ? "" : " AND createtime <= '$createtime_end'";
		if($username != "admin" || $dept_id){
			$where .= " AND dept_id in ($deptId)";
		}
		
		$fields = "p.program_name, p.id,
			sum(1) as total_good_sales_num,
			sum(p.price) as total_good_sales_money,
			sum(if(o.order_status = '1',1,0)) as actual_good_sales_num,
			sum(if(o.order_status = '1',p.price,0)) as actual_good_sales_money,
			sum(if(o.order_status != '1',1,0)) as other_good_sales_num,
			sum(if(o.order_status != '1',p.price,0)) as other_good_sales_money
		";
		
		//print_r($fields);
		$arrCount = Db::table('xy_shop_sale_program')->alias('p')->join('xy_order_info o','p.id = o.program_id')->Distinct(true)->field("o.program_id")->where($where)->select();
		$count = count($arrCount);
		$sql = "SELECT ".$fields." FROM xy_order_info o, xy_shop_sale_program p WHERE p.id = o.program_id AND ".$where."  GROUP BY o.program_id ORDER BY o.program_id asc LIMIT ".($page-1)*$limit.",".$limit;
		// print_r($sql);exit;
		//$dataList = Db::name('order_info')->fetchSql(true)->field($fields)->where($where)->limit(($page-1)*$limit,$limit)->order("createname asc")->group("createname")->select();
		$dataList = Db::query($sql);
		
		// foreach($dataList as $key=>&$val){
		// 	$val["dept_name"] = $department[$val["dept_id"]]["department_name"];
		// 	//实际销售单数/总订单数
		// 	$val["audit_rate2"] = ($val["actual_sales_num"]/$val["total_order_num"])*100;
		// 	$val["audit_rate"] = round($val["audit_rate2"],2)."%";;  //审核率
		// }
		
		$rowsList = count($dataList) ? $dataList : false;
        $arrCate["code"] = 0;
        $arrCate["msg"] = '';
        $arrCate["count"] = $count;
        $arrCate["data"] = $rowsList;
        return $arrCate;
		
	}





    
	/*
	* 获得一个数组，该数组是一维数组，存放id，名称，id和下一级别部门的id
	*/
	function getDepTreeData(){
		$DepTree = array();//一维数组
		$arr = Db::name('sys_department')->select();
		foreach($arr AS $v){
			$currentId = $v['department_id'];
			$arrSonId = Db::name('sys_department')->field('department_id')->where("department_pid=$currentId")->select();
			$strId = "$currentId";
			foreach($arrSonId AS $row){
				$strId .= "," . $row['department_id'];
			}
			$arrDepTree[$currentId] = Array(
				"id" => $v['department_id'],
				"pid" => $v['department_pid'],
				"name"=> $v['department_name'],
				"meAndSonId"=>$strId,
			);
		}
		//dump($arrDepTree);die;
		return $arrDepTree;
	}

	function getMeAndSubDeptID($arrDep,$dept_id){
		$arrId = explode(',',$arrDep[$dept_id]['meAndSonId']);
		//$str = "'" . $arrDep[$dept_id]['name'] . "',";
		$str = "'" . $arrDep[$dept_id]['id'] . "',";
		if( array_shift($arrId) ){
			foreach( $arrId AS $id ){
				$str .= $this->getMeAndSubDeptID($arrDep,$id);
			}
		}
		return $str;
		
	}
	
	
	









}